#!/bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto
MYSQL_HOME=/usr/bin/mysql
HIVE_HOME=/usr/bin/hive

echo "" >> ./sqoop_liujie.log
start_time=`date +"%Y-%m-%d %H:%M:%S"`
echo "dwd insert  start ${start_time}" >> ./sqoop_liujie.log

${HIVE_HOME} -S -e "
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;

--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;

-- This is hive sql script
insert into edu_dwd.fact_class_studying_stu_cnt partition (studying_date)
select id, school_id, subject_id, class_id, studying_student_count, studying_date
from edu_ods.class_studying_student_count;

-- 此表数据存在重复，取id最大的记录
insert into edu_dwd.fact_class_time_table partition (dt)
select id,
     class_id,
     morning_template_id,
     morning_begin_time,
     morning_end_time,
     afternoon_template_id,
     afternoon_begin_time,
     afternoon_end_time,
     evening_template_id,
     evening_begin_time,
     evening_end_time,
     use_begin_date,
     use_end_date,
     create_time,
     create_person,
     remark,
     dt
       from (
                  select *,
                         row_number() over (partition by t.class_id,t.use_begin_date,t.use_end_date order by t.create_time desc) rn
                  from edu_ods.tbh_class_time_table t
              ) o
where o.rn = 1
;


insert into edu_dwd.fact_course_table_upload_detail
select * from edu_ods.course_table_upload_detail;

--此表只取审批通过的数据
truncate table edu_dwd.fact_student_leave_apply;
insert into edu_dwd.fact_student_leave_apply
select id,
       class_id,
       student_id,
       audit_state,
       audit_person,
       audit_time,
       audit_remark,
       leave_type,
       leave_reason,
       substr(begin_time,1,19) as begin_time,
       begin_time_type,
       substr(end_time,1,19) as end_time,
       end_time_type,
       days,
       cancel_state,
       cancel_time,
       old_leave_id,
       leave_remark,
       valid_state,
       create_time
from edu_ods.student_leave_apply t where t.audit_state = 1;

--签到表，存在无用数据
truncate table edu_dwd.fact_student_signin_record;
insert into edu_dwd.fact_student_signin_record partition (signin_date)
select id,
       normal_class_flag,
       time_table_id,
       class_id,
       student_id,
       substr(signin_time,1,19) as signin_time,
       inner_flag,
       signin_type,
       share_state,
       inner_ip,
       ot.flag,
       signin_date
from (
         select o.*,
                min(o.signin_time) over (partition by o.class_id,o.student_id,o.signin_date ,o.flag) min_signin_time
         from (
                  select r.*,
                         case
                             when r.signin_time <= concat_ws(' ', r.signin_date, t.morning_end_time)
                                and (unix_timestamp(r.signin_time) - unix_timestamp(concat(r.signin_date,' ', morning_begin_time))) / 60 >= -40
                                 then 'morning'
                             when r.signin_time <= concat_ws(' ', r.signin_date, t.afternoon_end_time)
                                and (unix_timestamp(r.signin_time) - unix_timestamp(concat(r.signin_date,' ', afternoon_begin_time))) / 60 >= -40
                                 then 'afternoon'
                             when  r.signin_time <= concat_ws(' ', r.signin_date, t.evening_end_time)
                                 and (unix_timestamp(r.signin_time) - unix_timestamp(concat(r.signin_date,' ', evening_begin_time))) / 60 >= -40
                                 then 'evening'
                             else 'other' end as flag
                  from edu_ods.tbh_student_signin_record r,
                       edu_ods.tbh_class_time_table t
                  where t.class_id = r.class_id
                    -- and r.time_table_id = t.id 这个不能加，有些数据不能匹配
                    and r.signin_date >= t.use_begin_date
                    and r.signin_date <= t.use_end_date
                    -- and r.student_id = 125069
              ) o where o.flag in ('morning','afternoon','evening')
     ) ot
where ot.min_signin_time = ot.signin_time
;
"

start_time=`date +"%Y-%m-%d %H:%M:%S"`
echo "dwd insert  end ${start_time}" >> ./sqoop_liujie.log